package com.engine.core.rowMapper;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.parser.Feature;
import com.engine.core.domain.Direction;
import com.engine.core.domain.JavaFunction;
import com.engine.core.domain.Opt;
import com.engine.core.util.Utils;
import com.engine.dialect.DialectType;
import com.googlecode.aviator.AviatorEvaluator;
import org.stringtemplate.v4.ST;

import java.util.*;
import java.util.function.Function;

public class WhereBuildBiz {

     /**
       * 构造where部分或者having部分
      * GT大于，GE大于或等于，NE是不等于，EQ是等于，LT小于，LE小于或等于
      */

    /**
     * 前端追加where条件
     * @param rootSource 提交json字符串
     * @param columnAliasMap
     * @param dialectType
     * @return
     */
    public static String buildWhere(JSON rootSource,
                                    Map<String,String> columnAliasMap,DialectType dialectType){
        StringBuilder wherebuilder=new StringBuilder();
        JSONObject root=new JSONObject();
        if(JSONArray.class.isInstance(rootSource)){
            root.put("children",(JSONArray)rootSource);
            root.put("qtype","and");
        }else{
            root=(JSONObject)rootSource;
        }
        JSONArray children=root.getJSONArray("children");
        String type=root.getString("qtype");
        for(int a=0;a<children.size();a++){
            JSONObject item=children.getJSONObject(a);
            if(!Arrays.asList("or","and").contains(item.getString("qtype"))){
//                getConditionItem.apply(item).putParams(valueStack,paramColumnMap,paramTypeMap);
                wherebuilder.append(getConditionItem.apply(item).getWhere(columnAliasMap,dialectType));
            }else{
                wherebuilder.append(" ( ");
                wherebuilder.append(buildWhere(item,columnAliasMap,dialectType));
                wherebuilder.append(" ) ");
            }
            if(type.equalsIgnoreCase("or")){
                wherebuilder.append(" or ");
            }else{
                wherebuilder.append(" and ");
            }
        }
        if(type.equalsIgnoreCase("or")){
            wherebuilder.delete(wherebuilder.length()-3,wherebuilder.length());
        }else{
            wherebuilder.delete(wherebuilder.length()-4,wherebuilder.length());
        }
        return wherebuilder.toString();
    }

    /**
     * 前端param条件
     * @param root
     * @param valueStack
     * @param dateformat
     * @return
     */
    public static Map<String,String> repaceParams(JSONObject root,LinkedHashMap<String,Object> valueStack,String[] dateformat) throws Exception {
        Iterator<String> paramsSet=root.keySet().iterator();

        LinkedHashMap<String,String> repaceParams=new LinkedHashMap<>();
        Map<String,JavaFunction> javaFunctionMap=new HashMap<>();
        while(paramsSet.hasNext()){
              String typeParamName=paramsSet.next();
              MappedType mappedType= JdbcTypeBiz.getKeyType(typeParamName);
              String paramName=mappedType.getColumn();
              if(JSONObject.isValidObject(root.getString(typeParamName))){
                  JSONObject paramJson=root.getJSONObject(typeParamName);
                  String values[]=null;
                  String value=null;
                  if(paramJson.containsKey("value")){
                      if(JSONArray.isValidArray(paramJson.getString("value"))){
                          JSONArray valueArr=paramJson.getJSONArray("value");
                          values=new String[valueArr.size()];
                          for (int i = 0; i < valueArr.size(); i++) {
                              values[i]= valueArr.getString(i);
                          }
                      }else{
                          value=paramJson.getString("value");
                      }
                  }
                  if(paramJson.containsKey("function")){
                      String function=paramJson.getString("function");
                      JavaFunction javaFunction=new JavaFunction(function,paramName,1);
                      if(values!=null){
                          javaFunction.setComputedValue(values);
                      }else if(value!=null){
                          javaFunction.setComputedValue(value);
                      }
                      javaFunctionMap.put(paramName,javaFunction);
                  }
                      if(values!=null){
                          Object[] objs = new Object[values.length];
                          for (int i = 0; i < values.length; i++) {
                              objs[i]=Utils.convert(values[i], mappedType.getTypeClass(),dateformat);
                          }
                          valueStack.put(paramName,objs);
                      }else if(value!=null){
                          Object obj=Utils.convert(value, mappedType.getTypeClass(),dateformat);
                          valueStack.put(paramName,obj);
                      }
              }else if(JSONArray.isValidArray(root.getString(typeParamName))){
                  Set<String> sets=new LinkedHashSet<>();
                  sets.addAll(Arrays.asList(paramName));
                  JSONArray valueArr=root.getJSONArray(typeParamName);
                  String[] values=new String[valueArr.size()];
                  for (int i = 0; i < valueArr.size(); i++) {
                      values[i]= valueArr.getString(i);
                  }
                  Object[] objs = new Object[values.length];
                  for (int i = 0; i < values.length; i++) {
                      objs[i]=Utils.convert(values[i], mappedType.getTypeClass(),dateformat);
                  }
                  valueStack.put(paramName,objs);
              }else{
                  Object objs=Utils.convert(root.getString(typeParamName), mappedType.getTypeClass(),dateformat);
                  valueStack.put(paramName,objs);
              }
        }
        final Map<String,Object> env=new HashMap<>();
        env.put("params",valueStack);
        javaFunctionMap.entrySet().forEach(s->{
            JavaFunction function = s.getValue();
            env.put("value",function.getComputedValue());
            ST st=new ST(function.toString(),'$','$');
            env.entrySet().forEach(stp->{
                st.add(stp.getKey(),stp.getValue());
            });
            Object o=AviatorEvaluator.execute(st.render(), env);
            valueStack.put(s.getKey(),o);
        });
        return repaceParams;
    }

    /**
     * 构建orderBy
     * @param root
     * @return
     */
    public static String repaceOrder(JSONArray root){
          StringBuilder orderBy=new StringBuilder();
          for (int i = 0; i < root.size(); i++) {
                if(!JSON.isValidObject(root.getString(i))){
                     orderBy.append(root.getString(i));
                     orderBy.append(',');
                }
                if(JSON.isValidObject(root.getString(i))){
                    JSONObject orderItem=root.getJSONObject(i);
                    Object columns=orderItem.get("columns");
                    String order=root.getJSONObject(i).getString("order");
                    if(orderItem.containsKey("columns")){
                        if(columns instanceof JSONArray){
                            JSONArray valueArr=orderItem.getJSONArray("columns");
                            String[] values=new String[valueArr.size()];
                            for (int ii = 0; ii < valueArr.size(); ii++) {
                                values[ii]= valueArr.getString(ii);
                            }
                            orderBy.append(String.join(",",values));
                            orderBy.append(" "+order);
                        }else{
                            String valueArr=orderItem.getString("columns");
                            orderBy.append(valueArr+" "+order);
                        }
                        orderBy.append(",");
                    }
                }
          }
          if(orderBy.charAt(orderBy.length()-1) == ','){
              orderBy.deleteCharAt(orderBy.length()-1);
          }
          return orderBy.toString();
    }

    /**
     * 构建groupBy 或者 fields字段语句
     * @param root 所有查询列
     * @param columnAliasMap 列对应的表别名
     * @param functionMap 列转换方法
     * @return
     */
    public static String repaceColumns(JSON root,Map<String,String> columnAliasMap,Map<String,String> functionMap){
        StringBuilder groupBy=new StringBuilder();
        if(JSONArray.class.isInstance(root)){
            JSONArray array=(JSONArray)root;
            for (int i = 0; i < array.size(); i++) {
                if(array.get(i) instanceof JSONObject){
                    JSONObject columnObj=array.getJSONObject(i);
                    String column=columnObj.getString("column");
                    String function=columnObj.getString("function");
                    JSONArray args=columnObj.getJSONArray("args");
                    JavaFunction javaFunction=new JavaFunction(function,column,0);
                    javaFunction.setArgs(args);
                    functionMap.put(column,javaFunction.toString());
                    groupBy.append(column);
                    groupBy.append(',');
                }else{
                    String column=array.getString(i);
                    groupBy.append(column);
                    groupBy.append(',');
                }
            }
        }
        groupBy.deleteCharAt(groupBy.length()-1);
        return groupBy.toString();
    }

    public static String repaceGroupBy(JSON root,Map<String,String> columnAliasMap){
        StringBuilder groupBy=new StringBuilder();
        if(JSONArray.class.isInstance(root)){
            JSONArray array=(JSONArray)root;
            for (int i = 0; i < array.size(); i++) {
                    String column=array.getString(i);
                    groupBy.append(column);
                    groupBy.append(',');
            }
        }
        groupBy.deleteCharAt(groupBy.length()-1);
        return groupBy.toString();
    }

    static class ConditionItem{
         private String column;

         private String columnAlias;

         private String[] value;
         private String[] expTypes;
         private String javaType;
         private Opt qtype;

         private boolean containNull;

         private String[] exps;

        private boolean isExp;

        private String[] paramName;


        public ConditionItem() {
        }

        public String getWhere(Map<String,String> columnAliasMap, DialectType dialectType){
               if(this.isExp){
                   return qtype.getWhere(this.column,exps,paramName,columnAlias,columnAliasMap,containNull,dialectType);
               }else{
                   return qtype.getWhere(this.column,paramName,columnAlias,columnAliasMap,containNull,dialectType);
               }
        }


    }

    static class OrderItem{
        private String[] column;
        private Direction direction;
    }

    /**
     * 构造单个where条件
     */
   final static Function<JSONObject,ConditionItem> getConditionItem=(item)->{
         String isExp=Utils.nullDefault(item.getString("exp"),"");
         ConditionItem conditionItem=null;
         if(Utils.nullDefault(isExp,"false").equals("false")){
                conditionItem=new ConditionItem();
                conditionItem.qtype=Opt.getByName(item.getString("qtype"));
                conditionItem.column=item.getString("column");
             Object paramNameObj =  item.get("paramName");
             if(paramNameObj instanceof JSONArray){
                 JSONArray  paramNameArr=((JSONArray)paramNameObj);
                 String[] paramName =new String[paramNameArr.size()];
                 for (int i = 0; i < paramNameArr.size(); i++) {
                     paramName[i]=paramNameArr.getString(i);
                 }
                 conditionItem.paramName=paramName;
             }else{
                 if(paramNameObj instanceof String && Utils.nullDefault((String)paramNameObj).length()>0){
                     conditionItem.paramName=new String[]{(String)paramNameObj};
                 }else{
                     conditionItem.paramName=new String[]{conditionItem.column};
                 }
             }
                conditionItem.columnAlias=Utils.nullDefault(item.getString("columnAlias"),"");
                conditionItem.containNull=item.containsKey("containNull") && item.getBooleanValue("containNull")?true:false;
                if(JSONArray.isValidArray(item.getString("value"))){
                    JSONArray valueArr=item.getJSONArray("value");
                    String values[]=new String[valueArr.size()];
                    for (int i = 0; i < valueArr.size(); i++) {
                        values[i]= valueArr.getString(i);
                    }
                   conditionItem.value= values;
                }else{
                   conditionItem.value=new String[]{item.getString("value")};
                }
                conditionItem.isExp=false;
         }else{
             conditionItem=new ConditionItem();
             conditionItem.qtype=Opt.getByName(item.getString("qtype"));
             conditionItem.column=item.getString("column");
             Object paramNameObj =  item.get("paramName");
             if(paramNameObj instanceof JSONArray){
                 JSONArray  paramNameArr=((JSONArray)paramNameObj);
                 String paramName[]=new String[paramNameArr.size()];
                 for (int i = 0; i < paramNameArr.size(); i++) {
                     paramName[i]=paramNameArr.getString(i);
                 }
                 conditionItem.paramName=paramName;
             }else{
                 if(paramNameObj instanceof String && Utils.nullDefault((String)paramNameObj).length()>0){
                     conditionItem.paramName=new String[]{(String)paramNameObj};
                 }else{
                     conditionItem.paramName=new String[]{conditionItem.column};
                 }
             }
             conditionItem.columnAlias=Utils.nullDefault(item.getString("columnAlias"),"");
             conditionItem.containNull=item.containsKey("containNull") && item.getBooleanValue("containNull")?true:false;
             conditionItem.expTypes=Utils.nullDefault(item.getString("expTypes"),"").split(",");
             if(JSONArray.isValidArray(item.getString("value"))){
                 JSONArray valueArr=item.getJSONArray("value");
                 String[] values =new String[valueArr.size()];
                 for (int i = 0; i < valueArr.size(); i++) {
                     values[i]= valueArr.getString(i);
                 }
                 conditionItem.value= values;
             }else{
                 conditionItem.value=new String[]{item.getString("value")};
             }
             if(JSONArray.isValidArray(item.getString("exp"))){
                 JSONArray expArr=item.getJSONArray("exp");
                 String[] exps =new String[expArr.size()];
                 for (int i = 0; i < expArr.size(); i++) {
                     exps[i]= expArr.getString(i);
                 }
                 conditionItem.exps= exps;
             }else{
                 conditionItem.exps=new String[]{item.getString("exp")};
             }
             conditionItem.isExp=true;
         }
         return  conditionItem;
    };






    public static void main(String[] args) {
        Map<String,String> columnAliasMap=new HashMap<>();
        DialectType dialectType=DialectType.newInstance("mysql");
        columnAliasMap.put("age","cb_");
        System.out.println(Opt.IN.getWhere("user",new String[]{"user_1"},"",columnAliasMap,true,dialectType));
        System.out.println(Opt.NIN.getWhere("user",new String[]{"user_2"},"a",columnAliasMap,true,dialectType));
        System.out.println(Opt.EQ.getWhere("age",new String[]{"user_3"},"a",columnAliasMap,true,dialectType));
        System.out.println(Opt.BETWEEN.getWhere("age",new String[]{"user_4"},"a",columnAliasMap,true,dialectType));

        System.out.println(Opt.BETWEEN.getWhere("age",new String[]{"fun(:name)","age"},new String[]{"name_query"},"cq",columnAliasMap,true,dialectType));

//        String ss="{\"zz1\":\"123\",\"www0\":\"456\",\"aaa\":\"222\",\"hhg\":\"123\",\"child\":[{\"zz1\":\"123\",\"www0\":\"456\",\"aaa\":\"222\",\"hhg\":\"123\"}]}";
//        String ss2="[{\"zz1\":\"123\",\"www0\":\"456\",\"aaa\":\"222\",\"hhg\":\"123\"}]";
//        System.out.println(JSONObject.parseObject(ss, Feature.OrderedField));
//        System.out.println(JSONObject.parseObject(ss));
//        System.out.println(JSONArray.parseArray(ss2,Feature.OrderedField));
//        System.out.println(VType.valueOf("value"));
//
        String ss3="{ \n" +
                "                \"qtype\":\"and\",\n" +
                "                \"children\":[{\n" +
                "                    \"column\":\"name\",\n" +
                "                    \"paramName\":\"name_other\",\n" +
                "                    \"value\":[\"'123'\",\"'456'\",\"'789'\",\"'abc'\"],\n" +
                "                    \"qtype\":\"between\",\n" +
                "                    \"expTypes\":\"I,S,S,S\",\n" +
                "                    \"exp\":[\"exp(?,?,?,birthday)\",\"exp(?,birthday)\"]\n" +
                "                },{\n" +
                "                    \"column\":\"age\",\n" +
                "                    \"columnAlias\":\"na_\",\n" +
                "                    \"paramName\":\"aga\",\n" +
                "                    \"value\":[\"20\",\"30\"],\n" +
                "                    \"qtype\":\"in\"\n" +
                "                },{\n" +
                "                    \"column\":\"age\",\n" +
                "                    \"qtype\":\"le\",\n" +
                "                    \"paramName\":\"I_agt\",\n" +
                "                    \"value\":[\"20\"]\n" +
                "                }," +
                "                {" +
                "                    \"qtype\":\"or\",\n" +
                "                    \"children\":[\n"+
                "                         { \"qtype\":\"and\",\n"+
                "                           \"children\":[{ \n"+
                "                                            \"value\":[\"2015-10-01\",\"2015-12-01\"], \n"+
                "                                            \"column\":\"birthday\", \n"+
                "                                            \"paramName\":\"T_birthday\", \n"+
                "                                            \"qtype\":\"between\" \n"+
                "                                          },{\n"+
                "                                             \"column\":\"firstname\",\n"+
                "                                              \"qtype\":\"eq\",\n"+
                "                                              \"value\":[\"wangjun\"]\n"+
                "                                          }]\n"+
                "                         },{\n"+
                "                            \"qtype\":\"or\", \n"+
                "                            \"children\":[\n"+
                "                                          {\n"+
                "                                             \"value\":[\"2015-10-01\",\"2015-12-01\"],\n"+
                "                                             \"column\":\"birthday\",\n"+
                "                                             \"paramName\":\"T_birthday2\", \n"+
                "                                             \"qtype\":\"between\" \n"+
                "                                           },{ \n"+
                "                                             \"column\":\"firstname\",\n"+
                "                                             \"qtype\":\"eq\",\n"+
                "                                             \"value\":[\"wang\"]\n"+
                "                                            }\n"+
                "                                       ]\n"+
                "                            }\n"+
                "                        ]\n"+
                "                }]\n" +
                "        }";



        LinkedHashMap<String,String[]> whereContext=new LinkedHashMap<>();
        LinkedHashMap<String,Set<String>> paramColumnMap=new LinkedHashMap<>();
        LinkedHashMap<String,String> paramTypeMap=new LinkedHashMap<>();
        String sourcess=buildWhere(JSONObject.parseObject(ss3, Feature.OrderedField),columnAliasMap,dialectType);
        System.out.println(JSONObject.toJSONString(whereContext));
        System.out.println(JSONObject.toJSONString(paramColumnMap));
        System.out.println(JSONObject.toJSONString(paramTypeMap));
        System.out.println(JSONObject.toJSONString(sourcess));

        System.out.println("================================================================================");

        String ss4="{" +
                      "\"I_current\":{ \n"+
                                "\"value\":[\"123\",\"456\",\"'789'\",\"'abc'\"],\n"+
                     "           \"expTypes\":\"T,S\",\n" +
                                "\"exp\":[\"now(),?,?\"]\n"+
                      "},\n"+
                      "\"age_other\":{ \n"+
                            "\"value\":[\"123\",\"456\"],\n"+
                            "\"exp\":[\"?\"]\n"+
                      "},\n"+
                "\"age_22\":{ \n"+
                        "\"value\":[\"123\",\"456\"]\n"+
                "},\n"+
                "\"age_33\": \n"+
                "    [\"123\",\"456\"],\n"+
                "\n"+
                "\"age_44\": \n"+
                "    \"123\"\n"+
                "\n"+
                "}";

        LinkedHashMap<String,String[]> paramContext=new LinkedHashMap<>();
        LinkedHashMap<String,Set<String>> param2ColumnMap=new LinkedHashMap<>();
        paramTypeMap=new LinkedHashMap<>();
//        Map<String,String> sqlrepaceParams=repaceParams(JSONObject.parseObject(ss4,Feature.OrderedField),paramContext,param2ColumnMap,columnAliasMap,paramTypeMap);
        System.out.println(JSONObject.toJSONString(paramContext));
        System.out.println(JSONObject.toJSONString(param2ColumnMap));
//        System.out.println(JSONObject.toJSONString(sqlrepaceParams));
        System.out.println(JSONObject.toJSONString(paramTypeMap));


        System.out.println("================================================================================");
        String ss5="[" +
                    "{" +
                       "\"order\":\"asc\"," +
                       "\"value\":[\"123\",\"456\",\"789\"],"+
                "       \"expTypes\":\"S,S,I,S\",\n" +
                       "\"children\":[\"name_other\",\"age\",\"birthday\",\"exp(?,?)\",\"exp(?)\"]"+
                      "}," +
                        "{" +
                        "\"order\":\"desc\"," +
                        "\"children\":[\"lastname\",\"job\"]"+
                        "}" +
                "]";

        LinkedHashMap<String,String[]> orderContext=new LinkedHashMap<>();
        LinkedHashMap<String,Set<String>> order2ColumnMap=new LinkedHashMap<>();
        paramTypeMap=new LinkedHashMap<>();
        String repaceOrderReturn=repaceOrder(JSONArray.parseArray(ss5,Feature.OrderedField));
        System.out.println(JSONObject.toJSONString(orderContext));
        System.out.println(JSONObject.toJSONString(order2ColumnMap));
        System.out.println(JSONObject.toJSONString(columnAliasMap));
        System.out.println(repaceOrderReturn);
//
//        System.out.println("================================================================================");

        String ss6="[\"name_other\",\"age\",\"birthday\"]";
        LinkedHashMap<String,String[]> groupContext=new LinkedHashMap<>();
        String repaceGroupReturn=repaceColumns(JSONArray.parseArray(ss6,Feature.OrderedField),columnAliasMap,paramTypeMap);
        System.out.println(JSONObject.toJSONString(groupContext));
        System.out.println(repaceGroupReturn);


        String ss7="{\n"+
                 "\"value\":[\"123\",\"456\"],\n"+
                "       \"expTypes\":\"S,I\",\n" +
                 "\"children\":[\"name_other as test2\",\"age\",\"birthday\",\"exp(?,?)\"] \n" +
                "}";
        repaceGroupReturn=repaceColumns(JSONObject.parseObject(ss7,Feature.OrderedField),columnAliasMap,paramTypeMap);
        System.out.println(JSONObject.toJSONString(groupContext));
        System.out.println(JSONObject.toJSONString(paramTypeMap));
        System.out.println(repaceGroupReturn);


        System.out.println("$123$1111".replaceAll("\\$123\\$","abc"));

//        System.out.println(repaceOrder(JSONArray.parseArray(ss6,Feature.OrderedField)));
//        StringBuilder ssaa=new StringBuilder("exp(?,?,birthday)");
//        ssaa.replace(4,5,"123");
//        System.out.println(Utils.getFunStr("exp(?,?,birthday)",new String[]{"'123'","'456'"}));
    }
}
